create or replace procedure SP_SF_BRZHXX ( ad_BRID00 in number, --病人ID ad_CZY000 in number, --操作员ID as_CZYXM0 in char, --操作员姓名 as_JFLBMC in char, --账户类别、或称为交费类别 ad_XJYE00 in number, --病人现金余额 ad_ZZYE00 in number, --病人转账余额 ad_BJJE00 in number, --报警金额 ad_TYJE00 in number, --停用金额 ad_YXQFJE in number, --允许病人的欠费的最大金额 as_ZFFSMC in char, --支付方式 as_ZPH000 in char, --支票号 as_ZPKHYH in char, --出票的开户银行 as_ZPDWMC in char, --出票单位/出票人 as_YJJXH0 in char, --预交金票据号 as_CZBZ00 in char, --'0':交款,'1':退款 as_KTZZBZ in char default '0', --'0':可以退转账款,'1':不可以退转账款,'2':可以退转账款(不受医院参数控制) ad_PJH000 out number, --内部票据号 as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_COMMIT in char default 'Y', --是否提交 默认提交 as_ZPZH00 in char default null, --出票单位帐号或医保中心帐号 ad_CZYKS0 in number default null, --操作员科室 ad_ZPDWID in number default null, --转账单位ID as_JYLSH0 in char default null, --交易流水号 as_YKTSH0 in char default null, --健康通流水号 as_JYLXMC in char default null, --交易类型名称 as_JGBM00 in char default null, --机构编码 as_JGMC00 in char default null, --机构名称 as_DDLSH0 in char default null, --订单流水号 as_YYJSYH in char default null --医院结算银行 ) -- MODIFICATION HISTORY -- Person Date Comments -- lintj 2012.08.09 modify -- zhangwz 2012.08.27 对于 ls_YYID00 的引用从数字型变更为字符型。 -- zhanghr 2012.10.30 整改 -- zhanghr 2012.11.2 电子钱包不产生票据 ? -- jlg 2016.07.12 修改病人在住院时不能进行门诊预交金操作的处理 MZSF9-20160616-002 -- zhangyc 2017.06.26 增加微信支付(ZFFS00=24)和支付宝支付(ZFFS00=25); by MZSF9-20170328-001 -- jlg 2017.09.19 增加ZPDWID字段传参 MZSF9-20170919-002 -- jlg 2017.09.22 增加参数SF_SFYXZFBRTZZYE的处理 MZSF9-20170912-002 -- qks 2018.03.15 增加入参as_JYLSH0,as_YKTSH0,as_JYLXMC,as_JGBM00,as_JGMC00,as_DDLSH0; for MZSF9-20180315-001 -- liwm 2018.06.07 门诊收费存储过程统一添加调用参数日志 for MZSF9-20180604-003 -- qks 2018.07.06 根据参数SF_ZZSRSFNYXJTC控制：以转账方式交的预交金,是否允许以现金方式退还给病人; for MZSF9-20180702-001 -- qks 2018.09.28 增加参数SF_YJJSKDBQFZF控制：病人有效的担保和欠费记录是否自动作废; for MZSF9-20180927-003 -- pwt 2019.04.16 新增SF_BRJFB0.YYJSYH for MZSF9-20190411-001 -- jlg 2019.06.03 增加处理ad_CZYKS0允许为空，且为空时的处理 MZSF9-20190603-001 -- linzetao 2021.05.21 增加记录收退预交金时病人的有效卡卡面号 MZSF9-20210512-003 -- zhangyc 2021.09.17 增加校验 as ls_JFLBID BM_BRJFLB.JFLBID%type; ls_ZFFSBH BM_BRZFFS.ZFFSBH%type; ls_TMPZYE SF_BRZHXX.ZYE000%type; ls_TMPXJE SF_BRZHXX.XJYE00%type; ls_TMPZZE SF_BRZHXX.ZZYE00%type; ls_ZJE000 SF_BRJFB0.JFJE00%type; ls_PJH000 SF_BRJFB0.PJH000%type; ls_BRXM00 BM_BRXXB0.BRXM00%type; ls_BRXB00 BM_BRXXB0.BRXB00%type; ls_MZZHID SF_BRZHXX.MZZHID%type; ls_SFDJ00 SF_BRZHXX.SFDJ00%type; ls_JZDH00 SF_BRJFB0.JZDH00%type; ls_YHKJE0 SF_PJSYQK.YHKJE0%type; --银行卡金额 ls_ZZYE00 SF_PJSYQK.ZZJE00%type; ls_TYDWID BM_BRXXB0.TYDWID%type; ls_ZYZT00 BM_BRXXB0.ZYZT00%type; ls_FBBH00 number; ls_SSWUTSCL char(4); ls_YJJPJ0 char(4); --预交金票据与预交金退一样 ls_PLBHCS char(4); ls_CZRQ00 char(8); ls_CZSJ00 char(8); ls_count0 number(5); ls_DZQBJE SF_PJSYQK.DZQBJE%type; --电子钱包金额 ls_YYID00 XT_YYXX00.YYID00%type; ls_ZYJKT0 XT_XTCS00.VALUE0%type; --住院病人在门诊的交、退款限制 ls_YXZZTK XT_XTCS00.VALUE0%type; --是否允许自费病人转账退款 ls_ZZXJTC XT_XTCS00.VALUE0%type; --预交金已转帐收入，是否允许以现金退出 ls_DBQFZF XT_XTCS00.VALUE0%type; --门诊预交金收款,病人有效的担保和欠费记录是否自动作废 Vzxcgbz number; Vtsxx00 varchar2(200); Vxxxx00 varchar2(200); Vxxxlog varchar2(2000); ls_CZYKS0 BM_YGBM00.BMBH00%type; --部门编号 ls_KMH000 IC_YHXX00.KMH000%type; --卡面号 LS_TYZFCS varchar2(100); --门诊是否启用微信或支付宝接口N:都不开启 begin Vxxxlog := substr('AD_BRID00:'||nvl(to_char(AD_BRID00), 'null')||','||'AD_CZY000:'||nvl(to_char(AD_CZY000), 'null')||','||'AS_CZYXM0:'||nvl(to_char(AS_CZYXM0), 'null')||','||'AS_JFLBMC:'||nvl(to_char(AS_JFLBMC), 'null')||','||'AD_XJYE00:'||nvl(to_char(AD_XJYE00), 'null')||','||'AD_ZZYE00:'||nvl(to_char(AD_ZZYE00), 'null')||','||'AD_BJJE00:'||nvl(to_char(AD_BJJE00), 'null')||','||'AD_TYJE00:'||nvl(to_char(AD_TYJE00), 'null')||','||'AD_YXQFJE:'||nvl(to_char(AD_YXQFJE), 'null')||','||'AS_ZFFSMC:'||nvl(to_char(AS_ZFFSMC), 'null')||','||'AS_ZPH000:'||nvl(to_char(AS_ZPH000), 'null')||','||'AS_ZPKHYH:'||nvl(to_char(AS_ZPKHYH), 'null')||','||'AS_ZPDWMC:'||nvl(to_char(AS_ZPDWMC), 'null')||','||'AS_YJJXH0:'||nvl(to_char(AS_YJJXH0), 'null')||','||'AS_CZBZ00:'||nvl(to_char(AS_CZBZ00), 'null')||','||'AS_KTZZBZ:'||nvl(to_char(AS_KTZZBZ), 'null')||','||'AS_COMMIT:'||nvl(to_char(AS_COMMIT), 'null')||','||'AS_ZPZH00:'||nvl(to_char(AS_ZPZH00), 'null')||','||'AD_CZYKS0:'||nvl(to_char(AD_CZYKS0), 'null')||','||'AD_ZPDWID:'||nvl(to_char(AD_ZPDWID), 'null')||','||'AS_JYLSH0:'||nvl(to_char(AS_JYLSH0), 'null')||','||'AS_YKTSH0:'||nvl(to_char(AS_YKTSH0), 'null')||','||'AS_JYLXMC:'||nvl(to_char(AS_JYLXMC), 'null')||','||'AS_JGBM00:'||nvl(to_char(AS_JGBM00), 'null')||','||'AS_JGMC00:'||nvl(to_char(AS_JGMC00), 'null')||','||'AS_DDLSH0:'||nvl(to_char(AS_DDLSH0), 'null'),1,2000); SP_TransLog(sysdate,'SP_SF_BRZHXX',null,null,Vxxxlog); ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ls_YHKJE0 := 0; --银行卡金额 ls_DZQBJE := 0; --电子钱包金额 ls_ZZYE00 := ad_ZZYE00; --爱健康卡客户限制 select count(*) into ls_count0 from BM_JTKBR0 where BRID00=ad_BRID00 and KLB000='0'; if ls_count0>0 then if ad_CZY000<>99999 then as_YHMSG0:='爱健康卡客户,不能操作预交金!'; as_SYSMSG:='爱健康卡客户,不能操作预交金!'; rollback; return; end if; end if; ls_CZYKS0 := ad_CZYKS0; if ls_CZYKS0 is null then --如果传入的操作员科室为空，则取操作员对应部门编号 select nvl(max(BMBH00),0) into ls_CZYKS0 from BM_YGBM00 where YGBH00 = ad_CZY000; end if; --住院病人门诊交退款控制 0:交、退款都限制 1:不允许交款 2 不允许退款 3 交、退都不限制 select nvl(max(trim(VALUE0)),'0') into ls_ZYJKT0 from XT_XTCS00 where NAME00='SF_ZYBRSFYXJKTKZ'; --门诊预交金退款票据是否取预交金交款票据序号 Y:是N否，默认N select nvl(max(trim(VALUE0)),'N') into ls_YJJPJ0 from XT_XTCS00 where name00 = 'SF_YJJPJ0'; --SF_YJJTKPJLX:当SF_YJJPJ0=Y时门诊预交金退款票据类型(SF_PJSYQK.PLBH00)于交款票据类型是否一致 select nvl(max(trim(VALUE0)),'Y') into ls_PLBHCS from XT_XTCS00 where name00 = 'SF_YJJTKPJLX'; --四舍五入部分的金额特殊处理,Y:是 select nvl(max(trim(VALUE0)),'N') into ls_SSWUTSCL from XT_XTCS00 where name00 = 'SF_SSWUTSCL'; --门诊是否允许自费病人退转账余额 1是0否，默认0 select nvl(max(trim(VALUE0)),'0') into ls_YXZZTK from XT_XTCS00 where name00 = 'SF_SFYXZFBRTZZYE'; --预交金已转帐收入，是否允许以现金退出：Y允许 N不允许,默认为N select nvl(max(trim(VALUE0)),'N') into ls_ZZXJTC from XT_XTCS00 where name00 = 'SF_ZZSRSFNYXJTC'; --门诊预交金收款,病人有效的担保和欠费记录是否自动作废,0否 1是,默认为0 select nvl(max(trim(VALUE0)),'0') into ls_DBQFZF from XT_XTCS00 where name00 = 'SF_YJJSKDBQFZF'; --门诊是否启用微信或支付宝接口N:都不开启--2021.09.17--增加限制--begin--- LS_TYZFCS:=nvl(substrb(SF_SF_TYZD00('支付统一平台参数','Open_MZ'),1,10),'N'); if (LS_TYZFCS in ('Y','M','P')) and ((trim(as_ZFFSMC)='微信') or (trim(as_ZFFSMC)='支付宝')) and as_JYLSH0 is null then as_YHMSG0:=trim(as_ZFFSMC)||'支付错误,系统参数已开启联机支付,无法脱机支付充值或退款！'; as_SYSMSG:=as_YHMSG0; rollback; return; end if; --门诊是否启用微信或支付宝接口N:都不开启--2021.09.17--增加限制--end--- begin select SF_SF_GETYYID00() YYID00 into LS_YYID00 from dual; exception when others then LS_YYID00:='0'; end; select JFLBID into ls_JFLBID from BM_BRJFLB where JFLBMC = as_JFLBMC; select ZFFSBH into ls_ZFFSBH from BM_BRZFFS where ZFFSMC = as_ZFFSMC; if as_CZBZ00 = '1' then ls_JZDH00:=1; else ls_JZDH00:=0; end if; if ls_ZFFSBH in (8,24,25) then ls_YHKJE0 := ls_ZZYE00; ls_ZZYE00 := 0; end if; if ls_ZFFSBH = 18 then ls_DZQBJE := ls_ZZYE00; ls_ZZYE00 := 0; end if; --从BM_BRXXB0.ZYZT00获取病人住院状态 select BRXM00,BRXB00,ZYZT00,nvl(TYDWID,0),MZZHID,FBBH00 into ls_BRXM00,ls_BRXB00,ls_ZYZT00,ls_TYDWID,ls_MZZHID,ls_FBBH00 from BM_BRXXB0 where BRID00 = ad_BRID00; --从IC_YHXX00获取卡面号 select max(KMH000) into ls_KMH000 from IC_YHXX00 where BRID00=ad_BRID00 and ZT0000='1'; --病人如果处于住院状态，不允许进行门诊预交金操作 --加参数SF_ZYBRSFYXJKTKZ控制 0：限制交、退款 1限制交款 2限制退款 3 交、退都不限制 if (ls_ZYZT00='1') then if (ls_ZYJKT0='0') then as_YHMSG0:='该病人正在住院，系统设置不允许进行门诊预交金交退操作!'; as_SYSMSG:='该病人(BRID='||to_char(ad_BRID00)||')正在住院，系统设置不允许进行门诊预交金交退操作!'; rollback; return; elsif (ls_ZYJKT0='1') and (as_CZBZ00 ='0') then as_YHMSG0:='该病人正在住院，系统设置不允许进行门诊预交金交款操作!'; as_SYSMSG:='该病人(BRID='||to_char(ad_BRID00)||')正在住院，系统设置不允许进行门诊预交金交退操作!'; rollback; return; elsif (ls_ZYJKT0='2') and (as_CZBZ00 ='1') then as_YHMSG0:='该病人正在住院，系统设置不允许进行门诊预交金退款操作!'; as_SYSMSG:='该病人(BRID='||to_char(ad_BRID00)||')正在住院，系统设置不允许进行门诊预交金交退操作!'; rollback; return; end if; --其它情况允许操作 end if ; --0、判断该类病人的账户信息是否存在,若不存在，应该添加该病人的账户信息 select count(*) into ls_count0 from SF_BRZHXX where MZZHID=ls_MZZHID; if ls_count0=0 then as_YHMSG0:='该病人无门诊账户，不能操作'; as_SYSMSG:='数据异常'; rollback; return; end if; select SFDJ00,ZYE000,XJYE00,ZZYE00 into ls_SFDJ00,ls_TMPZYE,ls_TMPXJE,ls_TMPZZE from SF_BRZHXX where MZZHID=ls_MZZHID and JFLBID = ls_JFLBID; if ls_SFDJ00 <> '0' then as_YHMSG0:='该病人的账户的状态不是有效的状态,请先将该病人的账户转换成有效状态后,再进行本操作!!'; as_SYSMSG:=SQLERRM||'SFDJ00:='||ls_SFDJ00; rollback; return; end if; ls_ZJE000:=ad_XJYE00+ad_ZZYE00; if as_CZBZ00 = '1' then --若是退款的 if ls_TMPZYE <> ls_TMPXJE + ls_TMPZZE then as_YHMSG0:='该病人的账户的信息非法,请与系统管理员联系!'; as_SYSMSG:=SQLERRM||'该病人的帐户信息不合法,现金余额+转账余额不等于总余额!'; rollback; return; end if; if abs(ls_ZJE000) > ls_TMPZYE then as_YHMSG0:='该病人的账户余额不足于支付该病人本次操作的金额!'; as_SYSMSG:=SQLERRM||'目前的账户余额是:'||to_char(ls_TMPZYE); rollback; return; end if; if ad_XJYE00<0 and -1*ad_XJYE00>ls_TMPXJE+sign(instrb(ls_ZZXJTC,'Y'))*ls_TMPZZE then --退现金 as_YHMSG0:='该病人账户现金余额('||to_char(ls_TMPXJE)||')不足支付该病人本次退款的现金金额'; as_SYSMSG:=SQLERRM; rollback; return; end if; if ls_FBBH00=1 and ls_ZZYE00<0 and ls_YXZZTK<>'1' then as_YHMSG0:='自费病人不能做转账退款操作'; as_SYSMSG:=SQLERRM||'目前的账户转账余额是:'||to_char(ls_TMPZZE); rollback; return; end if; end if; select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; select SQ_SF_BRJFB0_DJH000.nextval into ad_PJH000 from dual; --1、添加病人交费记录 if as_CZBZ00='0' then insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,ZPZH00,CZYKS0,ZPDWID, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0,YYJSYH,KMH000) values(ad_PJH000,ad_BRID00,ls_JFLBID,ls_ZJE000,ls_ZFFSBH,as_ZFFSMC,as_ZPH000,as_ZPKHYH,as_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,as_CZBZ00,0,ls_JZDH00,1,ls_ZJE000,as_ZPZH00,ls_CZYKS0,ad_ZPDWID, as_JYLSH0,as_YKTSH0,as_JYLXMC,as_JGBM00,as_JGMC00,as_DDLSH0,as_YYJSYH,ls_KMH000); else insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0,ZPDWID, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0,YYJSYH,KMH000) values(ad_PJH000,ad_BRID00,ls_JFLBID,ls_ZJE000,ls_ZFFSBH,as_ZFFSMC,as_ZPH000,as_ZPKHYH,as_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,as_CZBZ00,0,ls_JZDH00,1,ls_ZJE000,ls_CZYKS0,ad_ZPDWID, as_JYLSH0,as_YKTSH0,as_JYLXMC,as_JGBM00,as_JGMC00,as_DDLSH0,as_YYJSYH,ls_KMH000); end if; SP_SF_BRJFB0_INSERT(ad_PJH000,ad_CZY000,ls_CZYKS0,'N',Vzxcgbz,as_YHMSG0,as_SYSMSG); --2、添加票据使用情况 if ls_ZFFSBH <> 18 then--电子钱包不产生票据 if as_CZBZ00 = '0' then --'0':交款,'1':退款 insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,HJJE00,CZYKS0) select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ad_XJYE00,ls_ZZYE00,ls_YHKJE0,ad_CZY000,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_BRXM00,ls_BRXB00,ad_BRID00,ls_DZQBJE,ad_XJYE00+ls_ZZYE00+ls_YHKJE0+ls_DZQBJE,ls_CZYKS0 from BM_PJLXB0 where PLBH00 = 7 ; --收预交金票据 if sql%notfound then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 7)'; rollback; return; end if; else if (trim(ls_YJJPJ0)='Y') and (trim(ls_PLBHCS)='Y') then Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,HJJE00,CZYKS0) select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ad_XJYE00,ls_ZZYE00,ls_YHKJE0,ad_CZY000,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_BRXM00,ls_BRXB00,ad_BRID00,ls_DZQBJE,ad_XJYE00+ls_ZZYE00+ls_YHKJE0+ls_DZQBJE,ls_CZYKS0 from BM_PJLXB0 where PLBH00 = 7 ; --退预交金票据 else Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,HJJE00,CZYKS0) select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ad_XJYE00,ls_ZZYE00,ls_YHKJE0,ad_CZY000,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_BRXM00,ls_BRXB00,ad_BRID00,ls_DZQBJE,ad_XJYE00+ls_ZZYE00+ls_YHKJE0+ls_DZQBJE,ls_CZYKS0 from BM_PJLXB0 where PLBH00 = 2 ; --退预交金票据 end if; if sql%notfound then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 2)'; rollback; return; end if; end if; SP_SF_PJSYQK_INSERT( ls_PJH000, --票据号 ad_CZY000, --输入：操作员编码 ls_CZYKS0, --操作员科室 'N', --是否提交 Vzxcgbz, --执行成功标志 1成功 0失败 Vtsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Vxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); end if; --2018.09.28 病人有效的担保和欠费记录自动作废 if ls_DBQFZF = '1' and as_CZBZ00 = '0' then update SF_BRDB00 set SFYX00 = 'N',JSCZY0=ad_CZY000,JSRXM0=as_CZYXM0, DBJSRQ=least(DBJSRQ,to_char(sysdate,'YYYYMMDD')),DBJSSJ=substrb(least(DBJSRQ||DBSJ00,to_char(sysdate,'YYYYMMDDHH24:MI:SS')),9,8) where BRID00=ad_BRID00 and JFLBID=1 and SFYX00='Y'; if sql%notfound=false then update SF_BRZHXX set DBJE00=0 where BRID00=ad_BRID00 and JFLBID=1; end if; end if; if as_COMMIT='Y' then commit; end if; exception when no_data_found then as_YHMSG0:='数据没有找到，请核对病人费别或病人的交费类别是否正确!'; as_SYSMSG:=SQLERRM; rollback; when others then as_YHMSG0:='系统出错，请查看详细信息。如有不明，请与管理员联系！'||SQLERRM; as_SYSMSG:=substr('执行过程SP_SF_BRZHXX时发生错误。'||SQLERRM,1,150); rollback; end; /